package com.borya.dao.impl;

import com.borya.dao.MerchantDAO;
import com.borya.framework.annotation.Component;
import com.borya.framework.core.ServerSqlSession;
import com.borya.framework.util.ObjectUtils;
import com.borya.model.db.*;
import com.borya.model.entity.ApplyRecord;
import com.borya.model.entity.ChangeAddressInfo;
import com.borya.model.entity.SupUserInfo;
import com.borya.util.DateUtils;
import com.borya.util.StringUtil;
import org.apache.log4j.Logger;

import java.util.*;
import java.util.stream.Collectors;

@Component("merchantDAO")
public class MerchantDAOImpl extends BaseDAOImpl implements MerchantDAO {

	private Logger log = Logger.getLogger(getClass());

	@Override
	public Merchant getByDealerId(final String dealerId) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sql = new StringBuilder(256);
			sql.append("select merchant_id,company_name,createtime,devMac,status");
			sql.append(",sign_time,sign_merge_picture,merchant_attribute,sign_picture,source_type");
			sql.append(" from tb_merchant where dealer_id=? limit 1");
			return session.executeQuery(sql.toString(), rs -> {
				if (rs != null && rs.next()) {
					Merchant merchant = new Merchant();
					merchant.setDealerId(dealerId);
					merchant.setMerchantId(rs.getString("merchant_id"));
					merchant.setCompanyName(rs.getString("company_name"));
					merchant.setCreateTime(rs.getLong("createtime"));
					merchant.setDevMac(rs.getString("devMac"));
					merchant.setStatus(rs.getInt("status"));
					merchant.setSignTime(rs.getLong("sign_time"));
					merchant.setSignMergePicture(rs.getString("sign_merge_picture"));
					merchant.setMerchant_attribute(rs.getInt("merchant_attribute"));
					merchant.setSignPicture(rs.getString("sign_picture"));
					merchant.setSourceType(rs.getInt("source_type"));
					return merchant;
				}

				return null;
			}, dealerId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public Merchant getByMerchantId(String merchantId) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sql = new StringBuilder(256);
			sql.append("select merchant_id,dealer_id,company_name,createtime,devMac,status,merchant_type");
			sql.append(",sign_time,sign_merge_picture,merchant_attribute,sign_picture");
			sql.append(",source_type,head_picture,regist_time,flag");
			sql.append(",user_type_id,business_licence,period,user_region");
			sql.append(" from tb_merchant ");
			sql.append(" where merchant_id=?");
			sql.append(" limit 1");
			return session.executeQuery(sql.toString(), rs -> {
				if (rs != null && rs.next()) {
					Merchant merchant = new Merchant();
					merchant.setDealerId(rs.getString("dealer_id"));
					merchant.setMerchantId(rs.getString("merchant_id"));
					merchant.setCompanyName(rs.getString("company_name"));
					merchant.setCreateTime(rs.getLong("createtime"));
					merchant.setDevMac(rs.getString("devMac"));
					merchant.setStatus(rs.getInt("status"));
					merchant.setSignTime(rs.getLong("sign_time"));
					merchant.setSignMergePicture(rs.getString("sign_merge_picture"));
					merchant.setMerchant_attribute(rs.getInt("merchant_attribute"));
					merchant.setSignPicture(rs.getString("sign_picture"));
					merchant.setSourceType(rs.getInt("source_type"));
					merchant.setUserTypeId(rs.getInt("user_type_id"));
					merchant.setBusinessLicence(rs.getString("business_licence"));
					merchant.setUserRegion(rs.getString("user_region"));
					merchant.setPeriod(rs.getString("period"));
					merchant.setHeadPicture(rs.getString("head_picture"));
					merchant.setMerchantType(rs.getInt("merchant_type"));
					merchant.setRegistTime(rs.getLong("regist_time"));
					merchant.setFlag(rs.getInt("flag"));
					return merchant;
				}

				log.error("DB not found Merchant,merchantId=" + merchantId);
				return null;
			}, merchantId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public List<Merchant> getLowerByDealerId(final String dealerId) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sql = new StringBuilder(256);
			sql.append("select company_name,createtime,devMac,status");
			sql.append(",sign_time,sign_merge_picture,standard_status,standard_time");
			sql.append(",(");
			sql.append("select phone from tb_user_client");
			sql.append(" where tb_user_client.dealer_id=tb_merchant.dealer_id ");
			sql.append(" and tb_user_client.is_main=1 LIMIT 1");
			sql.append(") phone ");
			sql.append(" from tb_merchant where popchannel=?");
			sql.append(" order by createtime desc");
			return session.executeQuery(sql.toString(), rs -> {
				List<Merchant> list = new ArrayList<Merchant>();
				if (rs != null) {
					while (rs.next()) {
						Merchant merchant = new Merchant();
						merchant.setDealerId(dealerId);
						merchant.setCompanyName(rs.getString("company_name"));
						merchant.setCreateTime(rs.getLong("createtime"));
						merchant.setDevMac(rs.getString("devMac"));
						merchant.setStatus(rs.getInt("status"));
						merchant.setSignTime(rs.getLong("sign_time"));
						merchant.setSignMergePicture(rs.getString("sign_merge_picture"));
						merchant.setPhone(rs.getString("phone"));
						merchant.setStandardStatus(rs.getInt("standard_status"));
						merchant.setStandardTime(rs.getLong("standard_time"));
						list.add(merchant);
					}
				}
				if (list.size() == 0) {
					log.error("DB not found Merchant,popchannel=" + dealerId);
				}
				return list;
			}, dealerId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public SupUserInfo getUserInfoByInviteCode(String inviteCode) {
		if (inviteCode.length() != 6 && inviteCode.length() != 8) {
			log.info("邀请码位数错误，6位/8位");
			return null;
		}
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sb = new StringBuilder();
			if (6 == inviteCode.length()) {
				sb.append("select a.source_type,a.userid,a.user_name,a.dealer_id,b.company_name,b.createtime ");
				sb.append("from tb_user_client a left join tb_merchant b ");
				sb.append("on a.dealer_id=b.dealer_id ");
				sb.append("where a.invite_code='");
				sb.append(inviteCode).append("' limit 1");
			} else {
				sb.append("select '' as userid,'' as user_name,0 source_type");
				sb.append(",dealer_id,createtime,dealer_name as company_name ");
				sb.append("from tb_user_referral ");
				sb.append("where referral_code='");
				sb.append(inviteCode).append("' limit 1");
			}
			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null && rs.next()) {
					SupUserInfo sup = new SupUserInfo();
					sup.setSourceType(rs.getInt("source_type"));
					sup.setCompanyName(rs.getString("company_name"));
					sup.setCreateTime(rs.getLong("createtime"));
					sup.setDealerId(rs.getString("dealer_id"));
					sup.setUserId(rs.getString("userid"));
					sup.setUserName(rs.getString("user_name"));
					return sup;
				}
				log.warn("db get nothing ,sql:" + sb.toString());
				return null;
			});
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public boolean addApplyPowerOrder(ApplyPower app) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			List<String> sql_list = new ArrayList<String>();
			StringBuilder sb = new StringBuilder();

			final String img = app.getImg();
			// img非空为营运商权限申请，attribute_status状态标识其是否申请中
			if (!StringUtil.isNULL(img)) {
				sb.append("update tb_merchant set ");
				sb.append("attribute_status=1 where dealer_id='");
				sb.append(app.getDealer_id()).append("'");
				sql_list.add(sb.toString());
				sb.delete(0, sb.length());
			}

			sb.append("insert into tb_apply_power set ");
			sb.append("`order_id`='").append(app.getOrder_id()).append("'");
			sb.append(",`dealer_id`='").append(app.getDealer_id()).append("'");
			sb.append(",`company_name`='").append(app.getCompany_name()).append("'");
			sb.append(",`create_time`=").append(app.getCreate_time());
			sb.append(",`before_power`=").append(app.getBefore_power());
			sb.append(",`apply_power`=").append(app.getApply_power());
			sb.append(",`img`='").append(img).append("'");
			sb.append(",`user_id`='").append(app.getUser_id()).append("'");
			sb.append(",`user_name`='").append(app.getUser_name()).append("'");
			sb.append(",`user_phone`=").append(app.getUser_phone());
			sb.append(",`status`=").append(app.getStatus());
			sb.append(",`scope_id`='").append(app.getScopeId()).append("'");
			sb.append(",`latitude`='").append(app.getLatitude()).append("'");
			sb.append(",`longitude`='").append(app.getLongitude()).append("'");
			sb.append(",`req_address`='").append(app.getReqAddress()).append("'");
			sql_list.add(sb.toString());
			int[] result = session.executeBatch(sql_list);
			return result != null && result.length > 0;
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return false;
		}
	}

	@Override
	public Map<String, String> getPower(String dealerId) {
		try (ServerSqlSession session = getSqlDataSource().session();) {

			StringBuilder sb = new StringBuilder();
			sb.append("select apply_power,img from tb_apply_power");
			sb.append(" where `dealer_id`=? and `status`=2 ORDER BY id desc LIMIT 1");
			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null && rs.next()) {
					Map<String, String> map = new HashMap<>();
					map.put("apply_power", rs.getString("apply_power"));
					map.put("img", rs.getString("img"));
					return map;
				}
				log.warn("db get nothing ,sql:" + sb.toString() + ",dealerId:" + dealerId);
				return null;
			}, dealerId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public List<Scope> getAllScopes(String type) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sb = new StringBuilder(
					"select scope_id,type,area,special_type,remark from tb_sell_scope_pool");
			if (ObjectUtils.nonNullOrEmpty(type)) {
				sb.append(" where type=").append(type);
			}
			sb.append(" order by type asc");
			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null) {
					List<Scope> scopes = new ArrayList<>();
					while (rs.next()) {
						Scope s = new Scope();
						s.setScopeId(rs.getString("scope_id"));
						s.setType(rs.getInt("type"));
						s.setArea(rs.getString("area"));
						s.setRemark(rs.getString("remark"));
						s.setSpecialType(rs.getInt("special_type"));
						scopes.add(s);
					}
					return scopes;
				}
				return null;
			});
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public List<Scope> getScopes(String dealerId, String type) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT a.scope_id,a.type,a.area,a.remark, ");
		sb.append("CASE WHEN ISNULL(b.dealer_id)=0 THEN 2  ");
		sb.append("WHEN ISNULL(c.`status`)=0 THEN 1 ELSE 0 END as isHave ");
		sb.append("FROM `tb_sell_scope_pool` a LEFT JOIN ");
		sb.append("(select dealer_id,scope_id from tb_merchant_scope_config where dealer_id=?) b ");
		sb.append(" on a.scope_id=b.scope_id ");
		sb.append(" LEFT JOIN (select * from tb_apply_power where dealer_id=? and `status`=1) c ");
		sb.append(" on a.scope_id=c.scope_id ");

		if (!StringUtil.isNULL(type))
			sb.append(" where a.type=").append(type);
		try (ServerSqlSession session = getSqlDataSource().session();) {
			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null) {
					List<Scope> scopes = new ArrayList<Scope>();
					while (rs.next()) {
						Scope s = new Scope();
						s.setScopeId(rs.getString("scope_id"));
						s.setType(rs.getInt("type"));
						s.setRemark(rs.getString("remark"));
						s.setArea(rs.getString("area"));
						s.setState(rs.getInt("isHave"));
						scopes.add(s);
					}
					return scopes;
				}
				log.warn("db get nothing ,sql:" + sb.toString() + ",dealerId:" + dealerId);
				return null;
			}, dealerId, dealerId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public boolean isScopeById(String dealerId, String scopeId, Integer type) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sql = new StringBuilder();
			sql.append("select count(1) from ");
			sql.append("(select scope_id from tb_merchant_scope_config where dealer_id=?) temp ");
			sql.append("left join tb_sell_scope_pool spool on temp.scope_id=spool.scope_id ");
			sql.append("where 1=1");
			if (!StringUtil.isNULL(scopeId)) {
				sql.append(" and temp.scope_id='").append(scopeId).append("'");
			}
			if (type != 0) {
				sql.append(" and spool.`type`=").append(type);
			}
			return session.executeQuery(sql.toString(), rs -> {
				return (rs != null && rs.next()) ? rs.getInt(1) > 0 : false;
			}, dealerId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return false;
		}
	}

	@Override
	public ApplyPower getApplyScope(String dealerId, String scopeId) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sb = new StringBuilder();
			sb.append("select * from tb_apply_power");
			sb.append(" where `dealer_id`=? and `scope_id`=? order by create_time desc LIMIT 1");
			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null && rs.next()) {
					ApplyPower ap = new ApplyPower();
					ap.setOrder_id(rs.getString("order_id"));
					ap.setDealer_id(rs.getString("dealer_id"));
					ap.setCompany_name(rs.getString("company_name"));
					ap.setStatus(rs.getInt("status"));
					ap.setCustomer_id(rs.getString("customer_id"));
					ap.setCustomer_name(rs.getString("customer_name"));
					ap.setCreate_time(rs.getLong("create_time"));
					ap.setModify_time(rs.getLong("modify_time"));
					ap.setBefore_power(rs.getInt("before_power"));
					ap.setApply_power(rs.getInt("apply_power"));
					ap.setImg(rs.getString("img"));
					ap.setUser_id(rs.getString("user_id"));
					ap.setUser_name(rs.getString("user_name"));
					ap.setUser_phone(rs.getString("user_phone"));
					ap.setScopeId(rs.getString("scope_id"));
					return ap;
				}
				log.warn("db get nothing ,sql:" + sb.toString() + ",dealerId:" + dealerId);
				return null;
			}, dealerId, scopeId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public List<ApplyRecord> getApplyRecords(String dealerId, Integer pageSize, Integer pageIndex) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sb = new StringBuilder(256);
			sb.append("select a.id,a.order_id,a.create_time,a.`status`,a.modify_time");
			sb.append(",a.reason,a.img,b.type,b.area");
			sb.append(" from tb_apply_power a LEFT JOIN tb_sell_scope_pool b on a.scope_id=b.scope_id");
			sb.append(" where a.dealer_id=? ");
			if (pageIndex > 0) {
				sb.append(" and a.id < ").append(pageIndex);
			}
			sb.append(" ORDER BY  a.create_time desc limit ?");
			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null) {
					List<ApplyRecord> arList = new ArrayList<ApplyRecord>();
					StringBuilder content = new StringBuilder();
					while (rs.next()) {
						ApplyRecord ar = new ApplyRecord();
						ar.setId(rs.getLong("id"));
						ar.setOrderId(rs.getString("order_id"));
						final long creatTime = rs.getLong("create_time");
						ar.setCreateTime(DateUtils.format(creatTime, "yyyy-MM-dd HH:mm:ss"));// 格式
						final long modifyTime = rs.getLong("modify_time");
						ar.setAuditTime(modifyTime > 0 ? DateUtils.format(modifyTime, "yyyy-MM-dd HH:mm:ss") : "--");// 格式
						ar.setReason(rs.getString("reason"));
						ar.setState(rs.getInt("status"));
						content.delete(0, content.length());
						boolean flag = StringUtil.isNULL(rs.getString("img"));
						content.append(flag ? "增加" : "权限开通");
						String area = rs.getString("area");
						// 1远特，2联通，3移动，4电信
						switch (rs.getInt("type")) {
						case 1:
							content.append("远特").append(area).append("区域合作");
							break;
						case 2:
							content.append("联通").append(area).append("区域合作");
							break;
						case 3:
							content.append("移动").append(area).append("区域合作");
							break;
						case 4:
							content.append("电信").append(area).append("区域合作");
							break;
						default:
							break;
						}
						ar.setContent(content.toString());
						arList.add(ar);
					}
					return arList;
				}
				log.warn("db get nothing ,sql:" + sb.toString() + ",dealerId:" + dealerId);
				return null;
			}, dealerId, pageSize);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public List<String[]> getAgentState(String dealerId, String type) {
		StringBuilder sb = new StringBuilder();
		sb.append("select sec.`type`,sum(sec.isHave) as state from ");
		sb.append("(SELECT a.`type`, ");
		sb.append("CASE WHEN ISNULL(b.dealer_id)=0 THEN 2 ");
		sb.append("WHEN ISNULL(c.`status`)=0 THEN 1 ELSE 0 END as isHave ");
		sb.append("FROM `tb_sell_scope_pool` a LEFT JOIN ");
		sb.append("(select dealer_id,scope_id from tb_merchant_scope_config where dealer_id=?) b ");
		sb.append(" on a.scope_id=b.scope_id ");
		sb.append("LEFT JOIN (select * from tb_apply_power where dealer_id=? and  `status`=1) c ");
		sb.append(" on a.scope_id=c.scope_id ");
		if (!StringUtil.isNULL(type))
			sb.append(" where a.type=").append(type);
		sb.append(") sec group by sec.`type`");

		try (ServerSqlSession session = getSqlDataSource().session();) {
			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null) {
					List<String[]> states = new ArrayList<String[]>();
					while (rs.next()) {
						String[] strs = new String[2];
						strs[0] = rs.getString("type");
						int state = rs.getInt("state");
						strs[1] = String.valueOf(state > 1 ? 2 : state);
						states.add(strs);
					}
					return states;
				}
				return null;
			}, dealerId, dealerId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public Scope findScopeById(String scopeId) {
		String sql = "select area,type,remark,scope_id,phone_model,pack_model,special_type,refuse_reason from tb_sell_scope_pool where scope_id=? limit 1";
		try (ServerSqlSession session = getSqlDataSource().session();) {
			return session.executeQuery(sql, rs -> {
				if (rs != null && rs.next()) {
					Scope scope = new Scope();
					scope.setArea(rs.getString("area"));
					scope.setType(rs.getInt("type"));
					scope.setRemark(rs.getString("remark"));
					scope.setScopeId(rs.getString("scope_id"));
					scope.setPhoneModel(rs.getString("phone_model"));
					scope.setPackModel(rs.getString("pack_model"));
					scope.setSpecialType(rs.getInt("special_type"));
					scope.setRefuseReason(rs.getString("refuse_reason"));
					return scope;
				}
				return null;
			}, scopeId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public List<String> merchantScops(String dealerId) {
		String sql = "select scope_id from tb_merchant_scope_config where dealer_id=?";
		try (ServerSqlSession session = getSqlDataSource().session();) {
			return session.executeQuery(sql, rs -> {
				List<String> scops = new ArrayList<>();
				if (rs != null) {
					while (rs.next()) {
						scops.add(rs.getString(1));
					}
					return scops;
				}
				return null;
			}, dealerId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public List<String> applyIngScop(String dealerID) {
		String sql = "select scope_id from tb_apply_power where dealer_id=? and `status`=1";
		try (ServerSqlSession session = getSqlDataSource().session();) {
			return session.executeQuery(sql, rs -> {
				List<String> scops = new ArrayList<>();
				if (rs != null) {
					while (rs.next()) {
						scops.add(rs.getString(1));
					}
					return scops;
				}
				return null;
			}, dealerID);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public ShqpInfo getShqpInfo(String dealerId) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sb = new StringBuilder(128);
			sb.append("select dealer_id,shpq_id,shqp_end_time,shqp_url,shqp_send_msg");
			sb.append(",modify_time,device_id,sign_url");
			sb.append(" from tb_merchant_shqp ");
			sb.append(" where dealer_id=? limit 1");
			return session.executeQuery(sb.toString(), rs -> {
				if (rs != null && rs.next()) {
					ShqpInfo shqpInfo = new ShqpInfo();
					shqpInfo.setDealerId(rs.getString("dealer_id"));
					shqpInfo.setShpqId(rs.getString("shpq_id"));
					shqpInfo.setShqpEndTime(rs.getInt("shqp_end_time"));
					shqpInfo.setShqpUrl(rs.getString("shqp_url"));
					shqpInfo.setShqpSendMsg(rs.getInt("shqp_send_msg"));
					shqpInfo.setModifyTime(rs.getLong("modify_time"));
					shqpInfo.setDeviceId(rs.getString("device_id"));
					shqpInfo.setSignUrl(rs.getString("sign_url"));
					return shqpInfo;
				}
				return null;
			}, dealerId);
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
			return null;
		}
	}

	@Override
	public boolean updShqpInfo(ShqpInfo shqpInfo, int type) {
		StringBuilder sb = new StringBuilder(128);
		if (type == 2) {
			sb.append("update tb_merchant_shqp set ");
			sb.append("shqp_end_time=").append(shqpInfo.getShqpEndTime());
			sb.append(",shqp_url='").append(shqpInfo.getShqpUrl()).append("'");
			sb.append(",sign_url='").append(shqpInfo.getSignUrl()).append("'");
			sb.append(",modify_time=").append(shqpInfo.getModifyTime());
			sb.append(" where dealer_id=? ");
		} else {
			sb.append("insert into tb_merchant_shqp set ");
			sb.append("dealer_id=?");
			sb.append(",shpq_id='").append(shqpInfo.getShpqId()).append("'");
			sb.append(",shqp_end_time=").append(shqpInfo.getShqpEndTime());
			sb.append(",shqp_url='").append(shqpInfo.getShqpUrl()).append("'");
			sb.append(",modify_time=").append(shqpInfo.getModifyTime());
			sb.append(",device_id='").append(shqpInfo.getDeviceId()).append("'");
			sb.append(",sign_url='").append(shqpInfo.getSignUrl()).append("'");
		}
		return executeUpdate(sb.toString(), shqpInfo.getDealerId());
	}

	@Override
	public List<ShqpInfo> timeEndList(int time) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sb = new StringBuilder(128);
			sb.append("select m.id,m.dealer_id,m.shqp_end_time,u.userid,u.source_type");
			sb.append(" from tb_merchant_shqp m ");
			sb.append(" left join tb_user_client u on m.dealer_id=u.dealer_id");
			sb.append(" where m.shqp_send_msg=0 and u.is_main=1 and m.shqp_end_time>0");
			sb.append(" and m.shqp_end_time < ?");
			return session.executeQuery(sb.toString(), rs -> {
				List<ShqpInfo> list = new ArrayList<>();
				if (rs != null) {
					while (rs.next()) {
						ShqpInfo shqpInfo = new ShqpInfo();
						shqpInfo.setId(rs.getInt("id"));
						shqpInfo.setDealerId(rs.getString("dealer_id"));
						shqpInfo.setShqpEndTime(rs.getInt("shqp_end_time"));
						shqpInfo.setUserId(rs.getString("userid"));
						shqpInfo.setSourceType(rs.getInt("source_type"));
						list.add(shqpInfo);
					}
				}
				return list;
			}, time);
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
			return null;
		}
	}

	@Override
	public boolean shqpMsg(List<ShqpInfo> dealerIds, List<Message> msgList) {
		List<String> sqlList = dealerIds.stream().map(x -> updShqpSql(x.getDealerId())).collect(Collectors.toList());

		List<String> msgSqlList = msgList.stream().map(x -> saveMsg(x)).collect(Collectors.toList());
		sqlList.addAll(msgSqlList);
		int[] res = executeBatch(sqlList);
		return res != null && res.length > 0;
	}

	private String updShqpSql(String dealerId) {
		StringBuilder sb = new StringBuilder("update tb_merchant_shqp set shqp_send_msg=1");
		sb.append(" where dealer_id='").append(dealerId).append("'");
		return sb.toString();
	}

	private String saveMsg(Message msg) {
		StringBuilder sql = new StringBuilder(256);
		sql.append("insert tb_message(");
		sql.append("msg_id,msg_type,sender,receiver,create_time,exp_time,content");
		sql.append(")values(");
		sql.append("'" + msg.getMsgId() + "'");
		sql.append(",'" + msg.getMsgType() + "'");
		sql.append(",'" + msg.getSender() + "'");
		sql.append(",'" + msg.getReceiver() + "'");
		sql.append(",'" + msg.getCreatTime() + "'");
		sql.append(",'" + msg.getExpTime() + "'");
		sql.append(",'" + msg.getContent() + "'");
		sql.append(")");
		return sql.toString();
	}

	@Override
	public int getfillStatus(String merchantId) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sql = new StringBuilder(256);
			sql.append("select fill_status");
			sql.append(" from tb_merchant where merchant_id=? limit 1");
			return session.executeQuery(sql.toString(), rs -> {
				return (rs != null && rs.next()) ? rs.getInt("fill_status") : -1;
			}, merchantId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return -1;
		}
	}

	@Override
	public boolean updateStatus(String merchantId, int status) {
		StringBuilder sql = new StringBuilder(256);
		sql.append("update tb_merchant set ");
		sql.append(" fill_status=" + status);
		sql.append(" where merchant_id='" + merchantId + "'");
		return executeUpdate(sql.toString());
	}

	@Override
	public String getUserTypeNickName(int id) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sql = new StringBuilder(256);
			sql.append("select nickname");
			sql.append(" from tb_user_type ");
			sql.append(" where id=? limit 1");
			return session.executeQuery(sql.toString(), rs -> {
				return (rs != null && rs.next()) ? rs.getString("nickname") : "";
			}, id);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return "";
		}
	}

	@Override
	public String getDealerIdResult(String orderId) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sql = new StringBuilder(256);
			sql.append("select result_str from tb_get_dealerid_log where order_id=? limit 1");
			return session.executeQuery(sql.toString(), (rs) -> (rs != null && rs.next()) ? rs.getString(1) : null,
					orderId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return "";
		}
	}

	@Override
	public ReplenishModel getReplenish(String merchantId) {
		try (ServerSqlSession session = getSqlDataSource().session()) {
			StringBuilder sql = new StringBuilder(256);
			sql.append("select t.company_name,t.store_address,r.cityName,r.cityCode,t.status,t.merchant_type ");
			sql.append(
					" from tb_merchant t left join tb_citycode_relate r on t.city_code = r.cityCode where t.merchant_id=? limit 1");
			return session.executeQuery(sql.toString(), rs -> {
				ReplenishModel model = new ReplenishModel();
				if (!Objects.isNull(rs) && rs.next()) {
					model.setCompanyName(rs.getString("t.company_name"));
					model.setSellCityName(rs.getString("r.cityName"));
					model.setStoreAddress(rs.getString("t.store_address"));
					model.setSellCityCode(rs.getString("r.cityCode"));
					model.setStatus(rs.getInt("status"));
					model.setMerchantType(rs.getInt("merchant_type"));
				}
				return model;
			}, merchantId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public ChangeAddressInfo getMerchantByUserId(String userId) {
		try (ServerSqlSession session = getSqlDataSource().session()) {
			StringBuilder sql = new StringBuilder(256);
			sql.append("SELECT d.merchant_id,d.phone,d.user_name,d.is_admin,m.user_region,m.store_address,"
					+ "m.regist_time,m.fill_status,d.is_main,d.source_type");
			sql.append("  FROM tb_user_client d LEFT JOIN tb_merchant m ON m.merchant_id = d.merchant_id "
					+ "WHERE d.userid =?");
			return session.executeQuery(sql.toString(), rs -> {
				ChangeAddressInfo model = new ChangeAddressInfo();
				if (!Objects.isNull(rs) && rs.next()) {
					model.setUserPhone(rs.getString("d.phone"));
					model.setUserName(rs.getString("d.user_name"));
					model.setUserAdmin(rs.getString("d.is_admin"));
					model.setMerchantId(rs.getString("d.merchant_id"));
					model.setOldAddress(rs.getString("m.user_region"));
					model.setOldCompleteAddress(rs.getString("m.store_address"));
					model.setRegistTime(rs.getLong("regist_time"));
					model.setFillStatus(rs.getInt("fill_status"));
					model.setIsMain(rs.getInt("is_main"));
					model.setSourceType(rs.getInt("source_type"));
				}
				return model;
			}, userId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public boolean insertChangeAddress(ChangeAddressInfo info) {
		StringBuilder sb = new StringBuilder(256);
		sb.append("insert into tb_merchant_change_address set ");
		sb.append("create_time=").append(info.getCreateTime());
		sb.append(",update_time=").append(info.getUpdateTime());
		sb.append(",order_id='").append(info.getOrderId()).append("'");
		sb.append(",user_id='").append(info.getUserId()).append("'");
		sb.append(",user_phone='").append(info.getUserPhone()).append("'");
		sb.append(",user_name='").append(info.getUserName()).append("'");
		sb.append(",user_admin='").append(info.getUserAdmin()).append("'");
		sb.append(",merchant_id='").append(info.getMerchantId()).append("'");
		sb.append(",order_source=").append(info.getOrderSource());
		sb.append(",old_address='").append(info.getOldAddress()).append("'");
		sb.append(",new_address='").append(info.getNewAddress()).append("'");
		sb.append(",is_adopt=").append(info.getIsAdopt());
		sb.append(",last_time=").append(info.getLastTime());
		sb.append(",old_complete_address='").append(info.getOldCompleteAddress()).append("'");
		sb.append(",new_complete_address='").append(info.getNewCompleteAddress()).append("'");
		return executeUpdate(sb.toString());
	}

	@Override
	public ChangeAddressInfo getChangeAddressLast(String userId) {
		try (ServerSqlSession session = getSqlDataSource().session()) {
			StringBuilder sql = new StringBuilder(256);
			sql.append("SELECT create_time FROM tb_merchant_change_address");
			sql.append(" where is_adopt=1 and user_id ='" + userId).append("' order by create_time desc LIMIT 1");
			return session.executeQuery(sql.toString(), rs -> {
				ChangeAddressInfo model = new ChangeAddressInfo();
				if (!Objects.isNull(rs) && rs.next()) {
					model.setCreateTime(rs.getLong("create_time"));
				}
				return model;
			});
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public boolean countChangeAddress(String merchantId) {
		try (ServerSqlSession session = getSqlDataSource().session();) {
			StringBuilder sql = new StringBuilder();
			sql.append("SELECT count(1) FROM tb_merchant_change_address");
			sql.append(" where is_adopt=0 and merchant_id =?");
			return session.executeQuery(sql.toString(), rs -> {
				return (rs != null && rs.next()) ? rs.getInt(1) > 0 : false;
			}, merchantId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return false;
		}
	}

	@Override
	public UserClientDetail getUserClientDetailByMerchantId(String userId) {
		try (ServerSqlSession session = getSqlDataSource().session()) {
			StringBuilder sql = new StringBuilder(256);
			sql.append("select * from tb_user_client_detail ");
			sql.append(" where userid=?");
			sql.append(" limit 1");
			return session.executeQuery(sql.toString(), rs -> {
				if (rs != null && rs.next()) {
					UserClientDetail detail = new UserClientDetail();
					detail.setIdentityCard(rs.getString("identity_card"));
					detail.setIdentityCardName(rs.getString("identity_card_name"));
					detail.setIdentityCardAddress(rs.getString("identity_card_address"));
					detail.setIdentifyPhotoHead(rs.getString("identify_photo_head"));
					detail.setIdentifyPhotoFront(rs.getString("identify_photo_front"));
					detail.setIdentifyPhotoReverse(rs.getString("identify_photo_reverse"));
					detail.setIdentifyPhotoHand(rs.getString("identify_photo_hand"));
					detail.setLivingStatus(rs.getInt("living_status"));
					return detail;
				}

				log.error("DB not found UserClientDetail,merchantId=" + userId);
				return null;
			}, userId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public UserClientDetail getUserClientByUserId(String userId) {
		try (ServerSqlSession session = getSqlDataSource().session()) {
			StringBuilder sql = new StringBuilder(256);
			sql.append("select * from tb_user_client ");
			sql.append(" where userid=?");
			sql.append(" limit 1");
			return session.executeQuery(sql.toString(), rs -> {
				if (rs != null && rs.next()) {
					UserClientDetail detail = new UserClientDetail();
					detail.setMerchantId(rs.getString("merchant_id"));
					detail.setIsMain(rs.getInt("is_main"));
					detail.setDealerId(rs.getString("dealer_id"));
					return detail;
				}

				log.error("DB not found tb_user_client,merchantId=" + userId);
				return null;
			}, userId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public UserClientDetail getUserClientByDealerId(String dealerId, Integer isMain) {
		try (ServerSqlSession session = getSqlDataSource().session()) {
			StringBuilder sql = new StringBuilder(256);
			sql.append("select * from tb_user_client ");
			sql.append(" where dealer_id=? and is_main=?");
			sql.append(" limit 1");
			return session.executeQuery(sql.toString(), rs -> {
				if (rs != null && rs.next()) {
					UserClientDetail detail = new UserClientDetail();
					detail.setMerchantId(rs.getString("merchant_id"));
					detail.setUserId(rs.getString("userid"));
					return detail;
				}

				log.error("DB not found tb_user_client,dealerId=" + dealerId + ",isMain=" + isMain);
				return null;
			}, dealerId, isMain);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

    @Override
    public List<Merchant> getMerchantByBusinessLicence(String businessLicence, Integer detailType) {
        try (ServerSqlSession session = getSqlDataSource().session()) {
            StringBuilder sql = new StringBuilder(256);
            sql.append("select merchant_id,dealer_id,company_name,createtime,devMac,status,merchant_type");
            sql.append(",sign_time,sign_merge_picture,merchant_attribute,sign_picture");
            sql.append(",source_type,head_picture,regist_time,flag");
            sql.append(",user_type_id,business_licence,period,user_region");
            sql.append(" from tb_merchant ");
            sql.append(" where business_licence=? and status = 0 and flag != -1 and detail_type = ? and merchant_type = 1 ");
            return session.executeQuery(sql.toString(), rs -> {
                List<Merchant> list = new ArrayList<>();
                if (rs != null) {
                    while (rs.next()) {
                        Merchant merchant = new Merchant();
                        merchant.setDealerId(rs.getString("dealer_id"));
                        merchant.setMerchantId(rs.getString("merchant_id"));
                        merchant.setCompanyName(rs.getString("company_name"));
                        merchant.setStatus(rs.getInt("status"));
                        merchant.setSignTime(rs.getLong("sign_time"));
                        merchant.setSignPicture(rs.getString("sign_picture"));
                        merchant.setDevMac(rs.getString("devMac"));
                        merchant.setSignMergePicture(rs.getString("sign_merge_picture"));
                        merchant.setMerchant_attribute(rs.getInt("merchant_attribute"));
                        merchant.setSourceType(rs.getInt("source_type"));
                        merchant.setCreateTime(rs.getLong("createtime"));
                        merchant.setUserTypeId(rs.getInt("user_type_id"));
                        merchant.setBusinessLicence(rs.getString("business_licence"));
                        merchant.setUserRegion(rs.getString("user_region"));
                        merchant.setHeadPicture(rs.getString("head_picture"));
                        merchant.setPeriod(rs.getString("period"));
                        merchant.setMerchantType(rs.getInt("merchant_type"));
                        merchant.setRegistTime(rs.getLong("regist_time"));
                        merchant.setFlag(rs.getInt("flag"));
                        list.add(merchant);
                    }
                }
                return list;
            }, businessLicence, detailType);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        }
    }
}